/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package marcin.belzowski.geoMap;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Hashtable;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import net.sf.cglib.proxy.Factory;

/**
 *
 * @author marcin
 */
public class Queries {

    EntityManagerFactory emf = Persistence.createEntityManagerFactory("PZ3");
    EntityManager em = emf.createEntityManager();
/***
 * zwraca ilość samochodów na minutę jaka przejechała prze ulicę streetName
 * @param streetName nazwa ulicy
 * @return ilość samochodóę na minutę
 */
    public Double findFrequency(String streetName) {
        //zwraca ilość samochodów które przejechało przez ulicę od początku prowadzenia statystyk
        Query q = em.createQuery("select count(ct.streetlaye) from Position p, City ct where true = crosses(makeline" +
                "(translate(p.point,-1,-1),translate( p.point,1,1) )" +
                "" +
                ",ct.theGeom) and ct.streetlaye = :streetName group by ct.streetlaye ");
        q.setParameter("streetName", streetName);
        List<Long> list = q.getResultList(); //ilość samochodów na ulicy



//zwraca czas pierwszego wpisu na danej ulicy
        Query minTime = em.createQuery("select p from Position p, City ct where true = crosses(makeline" +
                "(translate(p.point,-1,-1),translate( p.point,1,1) )" +
                "" +
                ",ct.theGeom) and ct.streetlaye = :streetName and p.datesent = (select min(s.datesent) from Position s)");
        minTime.setParameter("streetName", streetName);
        List<Position> pList = minTime.getResultList(); //najwcześniejszy wpis na danej ulicy

        Calendar date = new GregorianCalendar();
        @SuppressWarnings("static-access")
        //różnica w milisekundach pomiędzy aktualnym czasem a czsem pierwszego wpisu
        Long defrence = date.getInstance().getTimeInMillis() - pList.get(0).getDatesent().getTime();

        Double factor =(double)list.get(0)/(defrence/(3600000));


        return factor;


    }

    public Double findFrequency(String streetName, Date min, Date max) {
        String year;
        String month;
        String day;
        String hour;
        String minute;
//        year = Integer.toBinaryString(min.getYear();

        //zwraca ilość samochodów które przejechało przez ulicę od początku prowadzenia statystyk
        Query q = em.createQuery("select count(ct.streetlaye) from Position p, City ct where true = crosses(makeline" +
                "(translate(p.point,-1,-1),translate( p.point,1,1) )" +
                "" +
                ",ct.theGeom) and ct.streetlaye = :streetName  and p.datesent >= :minDate and p.datesent <= :maxDate" +
                "  group by ct.streetlaye");
        String tmp = min.toString();
        tmp ="'"+tmp+"'";
        q.setParameter("streetName", streetName);
        q.setParameter("minDate",min);
        q.setParameter("maxDate",max);
//        tmp="'";
//        tmp+= max.toString();
//        tmp+="'";
//        q.setParameter("minDate",tmp);
        

        List<Long> list = q.getResultList(); //ilość samochodów na ulicy



//zwraca czas pierwszego wpisu na danej ulicy
        Query minTime = em.createQuery("select p from Position p, City ct where true = crosses(makeline" +
                "(translate(p.point,-1,-1),translate( p.point,1,1) )" +
                "" +
                ",ct.theGeom) and ct.streetlaye = :streetName and p.datesent = (select min(s.datesent) from Position s)");
        minTime.setParameter("streetName", streetName);
        List<Position> pList = minTime.getResultList(); //najwcześniejszy wpis na danej ulicy

        Calendar date = new GregorianCalendar();
        @SuppressWarnings("static-access")
        //różnica w milisekundach pomiędzy aktualnym czasem a czsem pierwszego wpisu
        Long defrence = date.getInstance().getTimeInMillis() - pList.get(0).getDatesent().getTime();
        if (list.size() > 0) {
        Double factor =(double)list.get(0)/(defrence/(3600000));
        return factor;
        }
        else return 0.;


        


    }



    public Hashtable<String,Double> findFrequencyforAll(ArrayList<String> streets, Date min, Date max){
    Hashtable<String,Double> ht= new Hashtable<String, Double>();
    for(String s: streets){
        ht.put(s,findFrequency(s,min,max));
    }
    return ht;
    }

   public Hashtable<String,Double> findFrequencyforAll(ArrayList<String> streets){
    Hashtable<String,Double> ht= new Hashtable<String, Double>();
    for(String s: streets){
        ht.put(s,findFrequency(s));
    }
    return ht;
    }

    public Double findDistance(String name){
        Query q = em.createQuery("select distance(startpoint(c.theGeom),endpoint(c.theGeom)) from City c where" +
                " c.streetlaye = :streetName");
        q.setParameter("streetName", name);
        List<Double> list = q.getResultList();
        return list.get(0);


    }
    public ArrayList<String> getStreets(){
        Query q = em.createQuery("select c.streetlaye from City c");
        ArrayList<String> list = (ArrayList<String>)q.getResultList();

        return list;

    }
    public Date minDate(){
    Query q = em.createQuery("select p from Position p where  p.datesent = (select min(s.datesent) from Position s)");
    q.setMaxResults(1);
    List<Position> p = q.getResultList();
    
    return p.get(0).getDatesent();
    }
    public Date maxDate(){
         Query q = em.createQuery("select p from Position p where  p.datesent = (select max(s.datesent) from Position s)");
         q.setMaxResults(1);
    List<Position> p = q.getResultList();
    return p.get(0).getDatesent();
    }
}
